package org.csu.myPetStore.persistence.impl;

import org.csu.myPetStore.domain.*;
import org.csu.myPetStore.persistence.CartDAO;
import org.csu.myPetStore.persistence.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class CartDAOImpl implements CartDAO {

    private static final String getCartByUsername = "SELECT itemid,quantity from cart WHERE username = ?";
    private static final String addItemInfo = "SELECT productid,listprice,attr1,attr2,attr3,attr4,attr5 from item WHERE itemid = ?";
    private static final String deleteCartItemFromUserByItemId = "DELETE from cart WHERE itemid = ? AND username = ?";
    private static final String insertCartItemByIremId = "INSERT INTO CART (username,itemid,quantity) VALUES(?,?,?)";
    private static final String deleteAllItemByUsername = "DELETE FROM cart WHERE username = ?";
    private static final String getQuantityByItemid = "SELECT quantity from cart WHERE itemid = ? AND username = ?";
    private static final String updateQuantityByItemid = "UPDATE cart set quantity = ?  WHERE itemid = ? AND username = ?";
    private static final String addProductDescription = "SELECT descn,name from product WHERE productid = ?";

    @Override
    public Cart getCartByUsername(String username) {
        Cart cart = new Cart();

        try {
            CloseSource closeSource = new CloseSource();

            Connection connection = DBUtil.getConnection();
            PreparedStatement preparedStatement = connection.prepareStatement(getCartByUsername);
            preparedStatement.setString(1,username);

            ResultSet resultSet = preparedStatement.executeQuery();

            while (resultSet.next()){
                Item item = new Item();
                String itemId = resultSet.getString(1);
                item.setItemId(itemId);

                int quantity = resultSet.getInt(2);
                item.setQuantity(quantity);

                PreparedStatement preparedStatement1 = connection.prepareStatement(addItemInfo);
                preparedStatement1.setString(1,itemId);
                ResultSet resultSet1 = preparedStatement1.executeQuery();

                if(resultSet1.next()){

                    Product product = new Product();
                    PreparedStatement preparedStatement2 = connection.prepareStatement(addProductDescription);
                    preparedStatement2.setString(1,resultSet1.getString(1));
                    ResultSet resultSet2 = preparedStatement2.executeQuery();
                    if(resultSet2.next()){
                        int index = 0;
                        String s = resultSet2.getString(1);
                        for(int i=0;i<s.length();i++){
                            if(s.charAt(i)=='>'){
                                index = i;
                                break;
                            }
                        }
                        product.setDescription(resultSet2.getString(1).substring(index+1,s.length()));
                        product.setProductId(resultSet2.getString(2));
                    }
                    closeSource.closeSource(null,preparedStatement2,resultSet2,null);

                    product.setProductId(resultSet1.getString(1));
                    System.out.println(product.getDescription());



                    item.setProduct(product);
                    item.setListPrice(resultSet1.getBigDecimal(2));
                    item.setAttribute1(resultSet1.getString(3));
                    item.setAttribute2(resultSet1.getString(4));
                    item.setAttribute3(resultSet1.getString(5));
                    item.setAttribute4(resultSet1.getString(6));
                    item.setAttribute5(resultSet1.getString(7));

                }

                closeSource.closeSource(null,preparedStatement1,resultSet1,null);
                cart.loadItem(item,quantity,true);
            }

            closeSource.closeSource(connection,preparedStatement,resultSet,null);
        } catch (Exception e) {
            e.printStackTrace();
        }

        return cart;
    }

    @Override
    public void deleteCartItemFromUserByItemId(String itemId,String username) {
        try {
            Connection connection = DBUtil.getConnection();
            PreparedStatement preparedStatement = connection.prepareStatement(deleteCartItemFromUserByItemId);
            preparedStatement.setString(1,itemId);
            preparedStatement.setString(2,username);

            int result = preparedStatement.executeUpdate();
            if(result == 1)
                System.out.println("删除cart_item成功");

            CloseSource closeSource = new CloseSource();
            closeSource.closeSource(connection,preparedStatement,null,null);

        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    @Override
    public void insertCartItemByIremId(String itemId, int quantity,String username) {
        try {
            Connection connection = DBUtil.getConnection();
            PreparedStatement preparedStatement = connection.prepareStatement(insertCartItemByIremId);
            preparedStatement.setString(1,username);
            preparedStatement.setString(2,itemId);
            preparedStatement.setInt(3,quantity);

            int result = preparedStatement.executeUpdate();
            if(result == 1)
                System.out.println("插入cart_item成功");

            CloseSource closeSource = new CloseSource();
            closeSource.closeSource(connection,preparedStatement,null,null);

        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    @Override
    public void deleteAllItemByUsername(String username) {
        try {
            Connection connection = DBUtil.getConnection();
            PreparedStatement preparedStatement = connection.prepareStatement(deleteAllItemByUsername);
            preparedStatement.setString(1,username);

            int result = preparedStatement.executeUpdate();
            if(result == 1)
                System.out.println("清除购物车");

            CloseSource closeSource = new CloseSource();
            closeSource.closeSource(connection,preparedStatement,null,null);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    @Override
    public int getQuantityByItemid(String itemId,String username) {
        int num = 0;
        try {
            Connection connection = DBUtil.getConnection();
            PreparedStatement preparedStatement = connection.prepareStatement(getQuantityByItemid);
            preparedStatement.setString(1,itemId);
            preparedStatement.setString(2,username);

            ResultSet resultSet = preparedStatement.executeQuery();

            if(resultSet.next()){
                num = resultSet.getInt(1);
            }

            CloseSource closeSource = new CloseSource();
            closeSource.closeSource(connection,preparedStatement,resultSet,null);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return num;
    }

    //更新
    public void updateQuantityByItemid(int quantity,String itemId,String username){
        try {
            Connection connection = DBUtil.getConnection();
            PreparedStatement preparedStatement = connection.prepareStatement(updateQuantityByItemid);
            preparedStatement.setInt(1,quantity);
            preparedStatement.setString(2,itemId);
            preparedStatement.setString(3,username);

            int result = preparedStatement.executeUpdate();
            if(result == 1)
                System.out.println("更新cart_item成功");

            CloseSource closeSource = new CloseSource();
            closeSource.closeSource(connection,preparedStatement,null,null);

        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}
